using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class SPictureList : IPictureList
    {
        // Methods
        public bool DeleteByIdentity(int id)
        {
            bool flag = false;
            int num = 0;
            string cmdText = "";
            cmdText = "DELETE FROM pictureList WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10) };
            cmdParms[0].Value = id;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        if (num > 0)
                        {
                            flag = true;
                        }
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return flag;
                }
            }
        }

        public MPictureList GetRowByIdentity(string randomCode)
        {
            MPictureList list = null;
            string cmdText = "";
            cmdText = "SELECT id,pictureName,randomCode,isrdt FROM pictureList WHERE randomCode=@randomCode";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@randomCode", SqlDbType.NVarChar, 50) };
            cmdParms[0].Value = randomCode;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    try
                    {
                        list = new MPictureList();
                        list.Id = Convert.ToInt32(reader[0]);
                        list.PictureName = Convert.ToString(reader[1]);
                        list.RandomCode = Convert.ToString(reader[2]);
                        list.Isrdt = Convert.ToString(reader[3]);
                    }
                    catch (SqlException exception)
                    {
                        Console.WriteLine(exception.Message);
                    }
                }
            }
            return list;
        }

        public DataSet GetRows(string condition)
        {
            DataSet dataSet = new DataSet();
            string str = "SELECT id,pictureName,randomCode,isrdt FROM pictureList " + condition;
            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                try
                {
                    adapter.SelectCommand = new SqlCommand();
                    adapter.SelectCommand.CommandText = str;
                    adapter.SelectCommand.Connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    adapter.Fill(dataSet);
                }
                catch (SqlException exception)
                {
                    Console.WriteLine(exception.Message);
                }
            }
            return dataSet;
        }

        public int Insert(MPictureList data)
        {
            int num = 0;
            string cmdText = "INSERT INTO pictureList(pictureName,randomCode) VALUES(@pictureName, @randomCode)";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@pictureName", SqlDbType.NVarChar, 50), new SqlParameter("@randomCode", SqlDbType.NVarChar, 50) };
            cmdParms[0].Value = data.PictureName;
            cmdParms[1].Value = data.RandomCode;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return num;
                }
            }
        }

        public bool ModifyByIdentity(MPictureList data)
        {
            bool flag = false;
            int num = 0;
            string cmdText = "";
            cmdText = "UPDATE pictureList SET pictureName=@pictureName,randomCode=@randomCode WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10), new SqlParameter("@pictureName", SqlDbType.NVarChar, 50), new SqlParameter("@randomCode", SqlDbType.NVarChar, 50) };
            cmdParms[0].Value = data.Id;
            cmdParms[1].Value = data.PictureName;
            cmdParms[2].Value = data.RandomCode;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        if (num > 0)
                        {
                            flag = true;
                        }
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return flag;
                }
            }
        }
    }
}